from datetime import datetime

import pymysql

from settings import Config


def Mysql_connecter():
    conn = pymysql.connect(
        host=Config.HOST,
        user=Config.USER,
        password=Config.PASSWORD,
        database=Config.DATABASE,
        port=Config.PORT
    )
    return conn


# 发表文章
def articleAdd(userId, title, content):
    conn = Mysql_connecter()
    nowTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 创建游标对象
    cur = conn.cursor()
    sql = "insert into article(userId,title,content,insertTime) values ('%s','%s','%s','%s');"
    try:
        realSql = sql % (userId, title, content, nowTime)
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)
        return None


# 查询全部文章条数
def countArticle():
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select count(*) from article where isActive='1';"
    try:
        cur.execute(sql)
        articleNum = cur.fetchone()
        return articleNum
    except Exception as e:
        print(e)
        return None


# 分页查询文章
def QueryArticle(startPos, endPos):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from article where isActive='1' order by insertTime desc limit %s,%s;"
    try:
        realSql = sql % (startPos, endPos)
        cur.execute(realSql)
        articles = cur.fetchall()
        return articles
    except Exception as e:
        print(e)
        return None


# 根据文章id查询文章
def QueryArticleByArticleId(articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select a.*,b.id as userId,b.userName as userName from article a,users b where a.userId=b.id and a.id=%s;"
    try:
        readSql = sql % articleId
        cur.execute(readSql)
        article = cur.fetchone()
        return article
    except Exception as e:
        print(e)
        return None


# 更新收藏条数
def updateFavNumByArticleId(articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    updateFavNumSql = "update article set fav_num=(select count(*) from fav_table where articleId=%s and isActive='1') where id=%s;"
    try:
        realSql = updateFavNumSql % (articleId, articleId)
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)


# 根据用户id查询文章
def QueryArticleByUserId(userId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from article where userId=%s and isActive='1' order by insertTime desc"
    try:
        realSql = sql % userId
        cur.execute(realSql)
        article = cur.fetchall()
        return article
    except Exception as e:
        print(e)
        return None


# 根据文章id删除文章(逻辑)
def deleteArticleByArticleId(articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "update article set isActive='0' where id=%s"
    try:
        realSql = sql % articleId
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)


# 根据用户id查询收藏文章
def QueryFavByUserId(userId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select b.id as fav_id,a.* from article a,fav_table b where a.id=b.articleId and b.isActive='1' and a.isActive='1' and b.userId=%s"
    try:
        realSql = sql % userId
        cur.execute(realSql)
        article = cur.fetchall()
        return article
    except Exception as e:
        print(e)
        return None


# 根据收藏表id取消收藏(逻辑)
def deleteFavById(fav_id):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "update fav_table set isActive='0' where id=%s"
    try:
        realSql = sql % fav_id
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)


# 查询此用户是否收藏
def checkFav(userId, articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from fav_table where userId=%s and articleId=%s and isActive='1';"
    try:
        realSql = sql % (userId, articleId)
        cur.execute(realSql)
        fav = cur.fetchall()
        if fav:
            return True
        else:
            return False
    except Exception as e:
        print(e)
        return None


# 用户收藏文章
def articleFav(userId, articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    favSql = "insert into fav_table(userId,articleId) values(%s,%s);"
    updateFavNumSql = "update article set fav_num=(select count(*) from fav_table where articleId=%s and isActive='1') where id=%s;"
    queryFavNum = "select fav_num from article where id=%s;"
    try:
        # 插入收藏表
        favSqlReal = favSql % (userId, articleId)
        print(favSqlReal)
        cur.execute(favSqlReal)
        conn.commit()
        # 更新收藏数目
        updateFavNumSqlReal = updateFavNumSql % (articleId, articleId)
        print(updateFavNumSqlReal)
        cur.execute(updateFavNumSqlReal)
        conn.commit()
        # 查询收藏数目
        queryFavNumReal = queryFavNum % articleId
        cur.execute(queryFavNumReal)
        fav_num = cur.fetchone()
        return fav_num
    except Exception as e:
        print(e)
        return None


# 文章详情取消收藏文章
def articleDeFav(userId, articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    favSql = "delete from fav_table where userId=%s and articleId=%s;"
    updateFavNumSql = "update article set fav_num=(select count(*) from fav_table where articleId=%s) where id=%s;"
    queryFavNum = "select fav_num from article where id=%s;"
    try:
        # 删除收藏表
        favSqlReal = favSql % (userId, articleId)
        print(favSqlReal)
        cur.execute(favSqlReal)
        conn.commit()
        # 更新收藏数目
        updateFavNumSqlReal = updateFavNumSql % (articleId, articleId)
        cur.execute(updateFavNumSqlReal)
        conn.commit()
        # 查询收藏数目
        queryFavNumReal = queryFavNum % articleId
        print(queryFavNumReal)
        cur.execute(queryFavNumReal)
        fav_num = cur.fetchone()
        return fav_num
    except Exception as e:
        print(e)
        return None


# 添加评论
def addComment(userId, articleId, comment):
    conn = Mysql_connecter()
    nowTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 创建游标对象
    cur = conn.cursor()
    sql = "insert into comments (userId,articleId,comment,insertTime) values ('%s','%s','%s','%s');"
    try:
        realSql = sql % (userId, articleId, comment, nowTime)
        cur.execute(realSql)
        conn.commit()
    except Exception as e:
        print(e)
        return None


# 查询评论
def QueryComments(articleId):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select a.*,b.userName from comments a,users b where a.userId=b.id and a.articleId='%s' and a.isActive='1';"
    try:
        realSql = sql % articleId
        cur.execute(realSql)
        comments = cur.fetchall()
        return comments
    except Exception as e:
        print(e)
        return None


# 根据文章标题模糊查询
def QueryArticleByTitle(title):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select a.*,b.userName from article a,users b where a.userId=b.id and a.title like '%" + str(title) + "%' and a.isActive='1';"
    try:
        cur.execute(sql)
        article = cur.fetchall()
        return article
    except Exception as e:
        print(e)
        return None
